<?php
class omeanalysts_mdl_ome_dealerstock extends dbeav_model{

    //var $has_export_cnf = true;

    var $export_name = '经销商仓库存报表';

    public function table_name($real=false){
        $table_name = 'ome_delivery';
        if($real){
            return kernel::database()->prefix.$table_name;
        }else{
            return $table_name;
        }
    }

    public function count($filter=null){
        $where = $this->_filter($filter); 

        $sql = "SELECT t.branchname,t.AXCustomer,t.customername,t.bn,t.`code`,t.material_name,t.specifications,IFNULL(t.nums,0) rnums,IFNULL(t1.nums,0) ionums,IFNULL(t.nums,0)-IFNULL(t1.nums,0) nums from (
            SELECT  bh.`name` branchname ,f.AXCustomer,f.customername,item.bn, bc.`code`,bm.material_name,ext.specifications,sum(item.num) nums
            from sdb_ome_reship a
            inner join sdb_ome_reship_items item on a.reship_id=item.reship_id 
            left join sdb_ome_shop f on a.shop_id=f.shop_id
            left JOIN sdb_material_basic_material_ext ext on item.product_id=ext.bm_id 
            left join sdb_material_basic_material bm on item.product_id=bm.bm_id
            left join sdb_material_codebase bc on bm.bm_id=bc.bm_id
            left join sdb_ome_branch bh on a.branch_id=bh.branch_id
            left join sdb_ome_return_product rep on rep.return_bn=a.reship_bn           
            where bh.`name`='经销商仓' and f.AXCustomer!='' and a.is_check='14'
            group by bh.`name`  ,f.AXCustomer,item.bn,bc.`code`,bm.material_name,ext.specifications)t
          left join 
          (
            select bh.`name` branchname ,io.AXCustomer,io.customername,iots.bn, bc.`code`,bm.material_name,ext.specifications,sum(iots.nums) nums 
            from sdb_taoguaniostockorder_iso io
            inner join sdb_taoguaniostockorder_iso_items iots on io.iso_id=iots.iso_id
            left JOIN sdb_material_basic_material_ext ext on iots.product_id=ext.bm_id 
            left join sdb_material_basic_material bm on iots.product_id=bm.bm_id
            left join sdb_material_codebase bc on bm.bm_id=bc.bm_id
            left join sdb_ome_branch bh on io.branch_id=bh.branch_id
            where io.AXCustomer!='' and bh.`name`='经销商仓'  and io.iso_status='3'
            group by bh.`name`  ,io.AXCustomer,iots.bn,bc.`code`,bm.material_name,ext.specifications
          )t1 on t.branchname=t1.branchname and t.AXCustomer=t1.AXCustomer and t.bn=t1.bn and t.`code`=t1.`code` and t.material_name=t1.material_name and t.specifications=t1.specifications
          where ".$where." ";
        $row = $this->db->select($sql);
        return count($row);


    }

    public function getlist($cols='*', $filter=array(), $offset=0, $limit=-1, $orderType=null)
    {

        $where = $this->_filter($filter); 
   
        $sql = "SELECT t.branchname,t.AXCustomer,t.customername,t.bn,t.`code`,t.material_name,t.specifications,IFNULL(t.nums,0) rnums,IFNULL(t1.nums,0) ionums,IFNULL(t.nums,0)-IFNULL(t1.nums,0) nums from (
            SELECT  bh.`name` branchname ,f.AXCustomer,f.customername,item.bn, bc.`code`,bm.material_name,ext.specifications,sum(item.num) nums
            from sdb_ome_reship a
            inner join sdb_ome_reship_items item on a.reship_id=item.reship_id 
            left join sdb_ome_shop f on a.shop_id=f.shop_id
            left JOIN sdb_material_basic_material_ext ext on item.product_id=ext.bm_id 
            left join sdb_material_basic_material bm on item.product_id=bm.bm_id
            left join sdb_material_codebase bc on bm.bm_id=bc.bm_id
            left join sdb_ome_branch bh on a.branch_id=bh.branch_id
            left join sdb_ome_return_product rep on rep.return_bn=a.reship_bn           
            where bh.`name`='经销商仓' and f.AXCustomer!='' and a.is_check='14'
            group by bh.`name`  ,f.AXCustomer,item.bn,bc.`code`,bm.material_name,ext.specifications)t
          left join 
          (
            select bh.`name` branchname ,io.AXCustomer,io.customername,iots.bn, bc.`code`,bm.material_name,ext.specifications,sum(iots.nums) nums 
            from sdb_taoguaniostockorder_iso io
            inner join sdb_taoguaniostockorder_iso_items iots on io.iso_id=iots.iso_id
            left JOIN sdb_material_basic_material_ext ext on iots.product_id=ext.bm_id 
            left join sdb_material_basic_material bm on iots.product_id=bm.bm_id
            left join sdb_material_codebase bc on bm.bm_id=bc.bm_id
            left join sdb_ome_branch bh on io.branch_id=bh.branch_id
            where io.AXCustomer!='' and bh.`name`='经销商仓' and  io.iso_status='3'
            group by bh.`name`  ,io.AXCustomer,iots.bn,bc.`code`,bm.material_name,ext.specifications
          )t1 on t.branchname=t1.branchname and t.AXCustomer=t1.AXCustomer and t.bn=t1.bn and t.`code`=t1.`code` and t.material_name=t1.material_name and t.specifications=t1.specifications
          where ".$where." ";
        $rows = $this->db->selectLimit($sql,$limit,$offset);
        return $rows;
    }
    
    public function searchOptions(){
        $parentOptions = parent::searchOptions();
        $childOptions = array();
        return $Options = array_merge($parentOptions,$childOptions);
    }

    public function _filter($filter,$tableAlias=null,$baseWhere=null)
    {
        $where = array(1);
        if(isset($filter['AXCustomer']) && $filter['AXCustomer']){
            $where[]= ' t.AXCustomer  =\''.addslashes($filter['AXCustomer']).'\'';
        }  
        if(isset($filter['p_bn']) && $filter['p_bn'])
        {
            $where[] = ' t.bn =\''.addslashes($filter['p_bn']).'\'';
        }


        return implode($where,' AND ');
    }

    public function get_schema() 
    {
      //*:仓库 店铺编号  店铺名称  *:货号  *:条形码 *:货品名称  *:规格  *:库存
        $schema = array (
            'columns' => array (
               'branchname' =>
                array(
                    'type' => 'varchar(32)',
                    'required' => true,
                    'label' => '仓库',
                    'is_title' => true,
                    'width' => 125,
                    'searchtype' => 'nequal',
                    'editable' => false,
                    'filtertype' => 'normal',
                    'filterdefault' => true,
                    'in_list' => true,
                    'default_in_list' => false,
                ),
                'AXCustomer' =>
                array (
                  'type' => 'varchar(255)',
                  'label' => '客户编码',
                  'editable' => false,
                  'searchtype' => 'has',
                  'filtertype' => 'normal',
                  'filterdefault' => true,
                  'in_list' => true,
                  'default_in_list' => true,
                  'is_title' => true,
                  'width' => '120',
                ),
                'customername' =>
                array (
                  'type' => 'varchar(255)',
                  'label' => '客户名称',
                  'editable' => false,
                  'searchtype' => 'has',
                  'filtertype' => 'normal',
                  'filterdefault' => true,
                  'in_list' => true,
                  'default_in_list' => true,
                  'is_title' => true,
                  'width' => '120',
                ),
                 'bn' =>
                array (
                  'type' => 'varchar(255)',
                  'label' => '货号',
                  'editable' => false,
                  'searchtype' => 'has',
                  'filtertype' => 'normal',
                  'filterdefault' => true,
                  'in_list' => true,
                  'default_in_list' => true,
                  'is_title' => true,
                  'width' => '120',
                ),
                'code' =>
                array (
                  'type' => 'varchar(32)',
                  'required' => true,
                  'label' => '条形码',
                  'editable' => false,
                  'width' =>140,
                  'filtertype' => 'yes',
                  'filterdefault' => true,
                  'in_list' => true,
                  'default_in_list' => true,
                  'is_title' => true,
                ),
                'material_name' =>
                array (
                  'type' => 'varchar(64)',
                  'required' => true,
                  'label' => '物料名称',
                  'editable' => false,
                  'width' =>140,
                  'filtertype' => 'yes',
                  'filterdefault' => true,
                  'in_list' => true,
                  'default_in_list' => true,
                  'is_title' => true,
                ),
                'specifications' =>
                array (
                  'type' => 'varchar(30)',
                  'label' => '规格',
                  'editable' => false,
                  'is_title' => true,
                ),
                'rnums' =>
                array (
                  'type' => 'number',
                  'label' => '退货总数',
                  'required' => true,
                  'default' => 0,
                  'editable' => false,
                ),
                'ionums' =>
                array (
                  'type' => 'number',
                  'label' => '出库总数',
                  'required' => true,
                  'default' => 0,
                  'editable' => false,
                ),
                'nums' =>
                array (
                  'type' => 'number',
                  'label' => '剩余库存数',
                  'required' => true,
                  'default' => 0,
                  'editable' => false,
                ),
            ),

            'idColumn' => 'name',
            'in_list' => array (
                0 => 'branchname',
                1 => 'AXCustomer',
                2 => 'customername',
                3 => 'bn',
                4 => 'code',
                5 => 'material_name',
                6 => 'specifications',
                7 => 'rnums',
                8 => 'ionums',
                9 => 'nums',
            ),
            'default_in_list' => array (
                0 => 'branchname',
                1 => 'AXCustomer',
                2 => 'customername',
                3 => 'bn',
                4 => 'code',
                5 => 'material_name',
                6 => 'specifications',
                7 => 'rnums',
                8 => 'ionums',
                9 => 'nums',
            ),

        );
        return $schema;
    }

    /**
     * 获得日志类型(non-PHPdoc)
     * @see dbeav_model::getLogType()
     */
    public function getLogType($logParams) {
        $type = $logParams['type'];
        $logType = 'none';
        if ($type == 'export') {
            $logType = $this->exportLogType($logParams);
        }
        elseif ($type == 'import') {
            $logType = $this->importLogType($logParams);
        }
        return $logType;
    }
    /**
     * 导出日志类型
     * @param Array $logParams 日志参数
     */
    public function exportLogType($logParams) {
        $params = $logParams['params'];
        $type = 'report';
        if ($logParams['app'] == 'omeanalysts' && $logParams['ctl'] == 'ome_delivery') {
            $type .= '_purchaseReport_expressAnalysis';
        }
        $type .= '_export';
        return $type;
    }
    /**
     * 导入操作日志类型
     * @param Array $logParams 日志参数
     */
    public function importLogType($logParams) {
        $params = $logParams['params'];
        $type = 'report';
        if ($logParams['app'] == 'omeanalysts' && $logParams['ctl'] == 'ome_delivery') {
            $type .= '_purchaseReport_expressAnalysis';
        }
        $type .= '_import';
        return $type;
    }

    function io_title( $filter, $ioType='csv' )
    {
        switch( $filter ){
            case 'delivery':
                $this->oSchema['csv'][$filter] = array(
                    '*:仓库' => 'branchname',
                    '*:客户编码' => 'AXCustomer',
                    '*:客户名称' => 'customername',
                    '*:货号' => 'bn',
                    '*:条码' => 'code',
                    '*:物料' => 'material_name',
                    '*:规格' => 'specifications',
                    '*:退货总数' => 'rnums',
                    '*:出库总数' => 'ionums',
                    '*:剩余库存数' => 'nums',
                );
                break;
        }
        $this->ioTitle[$ioType][$filter] = array_keys( $this->oSchema[$ioType][$filter] );
        return $this->ioTitle[$ioType][$filter];
    }

    //根据查询条件获取导出数据
    public function getExportDataByCustom($fields, $filter, $has_detail, $curr_sheet, $start, $end){
          //根据选择的字段定义导出的第一行标题
          if($curr_sheet == 1){

              $title = array();
              foreach($this->io_title('delivery') as $k => $v ){
                  $title[] = mb_convert_encoding($v, 'GBK', 'UTF-8');
              }

              $data['content']['main'][0] = '"'.implode('","',$title).'"';
          }

          $where = $this->_filter($filter);

        $sql = "SELECT t.branchname,t.AXCustomer,t.customername,t.bn,t.`code`,t.material_name,t.specifications,IFNULL(t.nums,0) rnums,IFNULL(t1.nums,0) ionums,IFNULL(t.nums,0)-IFNULL(t1.nums,0) nums from (
            SELECT  bh.`name` branchname ,f.AXCustomer,f.customername,item.bn, bc.`code`,bm.material_name,ext.specifications,sum(item.num) nums
            from sdb_ome_reship a
            inner join sdb_ome_reship_items item on a.reship_id=item.reship_id 
            left join sdb_ome_shop f on a.shop_id=f.shop_id
            left JOIN sdb_material_basic_material_ext ext on item.product_id=ext.bm_id 
            left join sdb_material_basic_material bm on item.product_id=bm.bm_id
            left join sdb_material_codebase bc on bm.bm_id=bc.bm_id
            left join sdb_ome_branch bh on a.branch_id=bh.branch_id
            left join sdb_ome_return_product rep on rep.return_bn=a.reship_bn           
            where bh.`name`='经销商仓' and f.AXCustomer!='' and a.is_check='14'
            group by bh.`name`  ,f.AXCustomer,item.bn,bc.`code`,bm.material_name,ext.specifications)t
          left join 
          (
            select bh.`name` branchname ,io.AXCustomer,io.customername,iots.bn, bc.`code`,bm.material_name,ext.specifications,sum(iots.nums) nums 
            from sdb_taoguaniostockorder_iso io
            inner join sdb_taoguaniostockorder_iso_items iots on io.iso_id=iots.iso_id
            left JOIN sdb_material_basic_material_ext ext on iots.product_id=ext.bm_id 
            left join sdb_material_basic_material bm on iots.product_id=bm.bm_id
            left join sdb_material_codebase bc on bm.bm_id=bc.bm_id
            left join sdb_ome_branch bh on io.branch_id=bh.branch_id
            where io.AXCustomer!='' and bh.`name`='经销商仓' and io.iso_status='3'
            group by bh.`name`  ,io.AXCustomer,iots.bn,bc.`code`,bm.material_name,ext.specifications
          )t1 on t.branchname=t1.branchname and t.AXCustomer=t1.AXCustomer and t.bn=t1.bn and t.`code`=t1.`code` and t.material_name=t1.material_name and t.specifications=t1.specifications
          where ".$where." ";
            $omedelivery = $this->db->selectLimit($sql,$end,$start);

            foreach ($omedelivery as $k => $aFilter) {
                $exptmp_data = array();
                foreach ($aFilter as $col => $val) {
                    $aFilter[$col] = mb_convert_encoding($aFilter[$col], 'GBK', 'UTF-8');
                    $exptmp_data[] = $aFilter[$col];
                }

                $data['content']['main'][] = implode(',', $exptmp_data);
            }
            return $data;
        
    }
}
